﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace CMS_GYM
{
    public class clsPhieuThuTienDAO
    {
        #region Attribute
        /// <summary>
        /// Kết nối SQL Server
        /// </summary>
        protected CSQLServer sqlServer = new CSQLServer();
        protected clsPhieuThuTienChiTietDAO PhieuThuChiTietDAO = new clsPhieuThuTienChiTietDAO();
        #endregion

        /// <summary>
        /// Lay bang phieu thu theo nhieu tieu chi
        /// </summary>
        /// <param name="TuNgay"></param>
        /// <param name="DenNgay"></param>
        /// <param name="TuSo"></param>
        /// <param name="DenSo"></param>
        /// <param name="KyHieu"></param>
        /// <param name="NguoiTaoId"></param>
        /// <param name="MaKhachHang"></param>
        /// <param name="TuKhoa"></param>
        /// <param name="LoaiPhieu"></param>
        /// <returns></returns>
        public DataTable LayBang(DateTime? TuNgay, DateTime? DenNgay, string TuSo, string DenSo, string KyHieu, int? NguoiTaoId, string MaKhachHang, string TuKhoa, string LoaiPhieu)
        {
            string sql = "spSearchPhieuGiaoDich";
             string[] ParameterColection = new string[] {    
                                    "@TuNgay","@DenNgay","@TuSo","@DenSo","@KyHieu","@NguoiTaoId"
                                    ,"@MaKhachHang","@TuKhoa","@LoaiPhieu"
                                };
            Object[] valueofParameter = new Object[] {
                                    TuNgay,DenNgay,TuSo,DenSo,KyHieu,
                                    NguoiTaoId,MaKhachHang,TuKhoa,LoaiPhieu
                                };
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            return table;
        }

        public DataTable LayBangChiTiet(DateTime? TuNgay, DateTime? DenNgay, string TuSo, string DenSo, string KyHieu, int? NguoiTaoId, string MaKhachHang,string TaiKhoanNo,string TaiKhoanCo, string TuKhoa, string LoaiPhieu)
        {
            string sql = "spSearchPhieuGiaoDichChiTiet";
            string[] ParameterColection = new string[] {    
                                    "@TuNgay","@DenNgay","@TuSo","@DenSo","@KyHieu","@NguoiTaoId"
                                    ,"@MaKhachHang","@TaiKhoanNo","@TaiKhoanCo","@TuKhoa","@LoaiPhieu"
                                };
            Object[] valueofParameter = new Object[] {
                                    TuNgay,DenNgay,TuSo,DenSo,KyHieu,
                                    NguoiTaoId,MaKhachHang,TaiKhoanNo, TaiKhoanCo,TuKhoa,LoaiPhieu
                                };
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            return table;
        }

        /// <summary>
        /// Lay bang theo so phieu thu va ky hieu
        /// </summary>
        /// <param name="SoPhieuThu"></param>
        /// <param name="KyHieu"></param>
        /// <returns></returns>
        public DataTable LayBang(string SoPhieuThu, string KyHieu)
        {
            string sql = "spGetInfoPhieuThuTheoSoPhieuThuVaKyHieu";
            string[] ParameterColection = new string[] {    
                                    "@SoPhieuThu","@KyHieu"
                                };
            Object[] valueofParameter = new Object[] {
                                    SoPhieuThu,KyHieu
                                };
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            return table;
        }

        public clsPhieuThuTienDTO LayThongMotPhieuThu(string SoPhieuThu, string KyHieu)
        {
            clsPhieuThuTienDTO PhieuThu = new clsPhieuThuTienDTO();
            DataTable table = LayBang(SoPhieuThu, KyHieu);
            if (table != null && table.Rows.Count > 0)
            {
                PhieuThu = ChuyenDoi(table.Rows[0]);
                if (PhieuThu != null)
                {
                    PhieuThu.CacPTCT = PhieuThuChiTietDAO.LayDanhSach(PhieuThu.PhieuThuId);
                    if (PhieuThu.CacPTCT == null)
                    {
                        return null;
                    }
                }
                else
                {
                    return null;
                }
                return PhieuThu;
            }
            else
            {
                return null;
            }
        }

        public DataTable LayBangPhieuThuChiet(string SoPhieuThu, string KyHieu)
        {
            return PhieuThuChiTietDAO.LayBang(SoPhieuThu, KyHieu);
        }

        public DataTable LayBangPhieuThuChiet(decimal PhieuThuId)
        {
            return PhieuThuChiTietDAO.LayBang(PhieuThuId);
        }


        private clsPhieuThuTienDTO ChuyenDoi(DataRow row)
        {
            clsPhieuThuTienDTO PhieuThu = new clsPhieuThuTienDTO();
            try
            {
                PhieuThu.PhieuThuId = decimal.Parse(row["PhieuThuId"].ToString());
                PhieuThu.SoPhieuThu = row["SoPhieuThu"] == null ? string.Empty : row["SoPhieuThu"].ToString();
                PhieuThu.KyHieu = row["KyHieu"] == null ? string.Empty : row["KyHieu"].ToString();
                PhieuThu.Ky = row["Ky"] == null ? string.Empty : row["Ky"].ToString();
                PhieuThu.NgayXuat = DateTime.Parse(row["NgayXuat"].ToString());
                PhieuThu.KhachHangId = row["KhachHangId"] == null || string.IsNullOrEmpty(row["KhachHangId"].ToString()) ? null: (decimal?) decimal.Parse(row["KhachHangId"].ToString());
                PhieuThu.MaKhachHang = row["MaKhachHang"] == null ? string.Empty : row["MaKhachHang"].ToString();
                PhieuThu.HoTen = row["HoTen"] == null ? string.Empty : row["HoTen"].ToString();
                PhieuThu.MaSoThue = row["MaSoThue"] == null ? string.Empty : row["MaSoThue"].ToString();
                PhieuThu.DiaChi = row["DiaChi"] == null ? string.Empty : row["DiaChi"].ToString();
                PhieuThu.PhanTramThue = row["PhanTramThue"] == null ? 0 : int.Parse(row["PhanTramThue"].ToString());
                PhieuThu.TongTienThu = double.Parse(row["TongTienThu"].ToString());
                PhieuThu.TongTienHang = double.Parse(row["TongTienHang"].ToString());
                PhieuThu.TongTienHang = double.Parse(row["TongTienThue"].ToString());
                PhieuThu.TongChietKhau = double.Parse(row["TongChietKhau"].ToString());
                PhieuThu.GhiChu = row["GhiChu"] == null ? string.Empty : row["GhiChu"].ToString();
                PhieuThu.NguoiTaoId = int.Parse(row["NguoiTaoId"].ToString());
                PhieuThu.NgayTao = DateTime.Parse(row["NgayTao"].ToString());
                PhieuThu.NguoiChinhSuaId = row["NguoiChinhSuaId"] == null || string.IsNullOrEmpty(row["NguoiChinhSuaId"].ToString()) ? null : (int?)int.Parse(row["NguoiChinhSuaId"].ToString());
                PhieuThu.NgayChinhSua = row["NgayChinhSua"] == null || string.IsNullOrEmpty(row["NgayChinhSua"].ToString()) ? null : (DateTime?)DateTime.Parse(row["NgayChinhSua"].ToString());
                PhieuThu.TrangThai = row["TrangThai"] == null ? "ACTIVE" : row["TrangThai"].ToString();
                return PhieuThu;
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// Thêm thông tin Phiếu Xuất Bán Sỉ
        /// </summary>
        /// <param name="PhieuXuat">
        /// MaPhieuXuat                  nvarchar(10)
        /// NgayXuat                       smalldatetime
        /// MaNhanVienBanHang     nvarchar(10)
        /// TongTien                        float
        /// DaTra                             float
        /// KhachHang                     nvarchar(255)
        /// LoaiPhieuXuat                 nvarchar(100)
        /// TrangThai                       int
        /// </param>
        public int Them(clsPhieuThuTienDTO PhieuThu)
        {
            int i = -1;
            string sql = "spInsertPhieuThu";
            string[] ParameterColection = new string[] {    
                                    "@SoPhieuThu","@KyHieu","@Ky","@NgayXuat","@KhachHangId","@MaKhachHang"
                                    ,"@HoTen","@MaSoThue","@DiaChi","@PhanTramThue","@TongTienThu","@TongTienHang"
                                    ,"@TongTienThue","@TongChietKhau","@GhiChu","@NguoiTaoId"
                                };
            Object[] valueofParameter = new Object[] {
                                    PhieuThu.SoPhieuThu,PhieuThu.KyHieu,PhieuThu.Ky,PhieuThu.NgayXuat,PhieuThu.KhachHangId,PhieuThu.MaKhachHang,
                                    PhieuThu.HoTen,PhieuThu.MaSoThue, PhieuThu.DiaChi,PhieuThu.PhanTramThue, PhieuThu.TongTienThu,PhieuThu.TongTienHang,
                                    PhieuThu.TongTienThue,PhieuThu.TongChietKhau, PhieuThu.GhiChu,PhieuThu.NguoiTaoId
                                };
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            if(i!=-1)
            {
                PhieuThu.PhieuThuId=i;
                for (int k = 0; k < PhieuThu.CacPTCT.Count; k++)
                {
                    PhieuThu.CacPTCT[k].PhieuThuId = PhieuThu.PhieuThuId;
                    if (PhieuThuChiTietDAO.Them(PhieuThu.CacPTCT[k]) == -1)
                    {
                        return -1;
                    }
                    else
                    {
                        if (PhieuThu.CacPTCT[k].CongNoId != null && PhieuThu.CacPTCT[k].CongNoId > 0)
                        {
                            if (new clsCongNoCauLongDAO().CapNhatCongNoDaThuTien((decimal)PhieuThu.CacPTCT[k].CongNoId, PhieuThu.PhieuThuId, PhieuThu.CacPTCT[k].TienThu) == -1)
                            {
                                return -1;
                            }
                        }
                    }
                }
            }
            return i;
        }

        /// <summary>
        /// Thêm thông tin Phiếu Xuất Bán Sỉ
        /// </summary>
        /// <param name="PhieuXuat">
        /// MaPhieuXuat                  nvarchar(10)
        /// NgayXuat                       smalldatetime
        /// MaNhanVienBanHang     nvarchar(10)
        /// TongTien                        float
        /// DaTra                             float
        /// KhachHang                     nvarchar(255)
        /// LoaiPhieuXuat                 nvarchar(100)
        /// TrangThai                       int
        /// </param>
        public int Sua(clsPhieuThuTienDTO PhieuThu)
        {
            int i = -1;
            string sql = "spUpdatePhieuThu";
            string[] ParameterColection = new string[] {    
                                    "@PhieuThuId","@SoPhieuThu","@KyHieu","@Ky","@NgayXuat","@KhachHangId","@MaKhachHang"
                                    ,"@HoTen","@MaSoThue","@DiaChi","@PhanTramThue","@TongTienThu","@TongTienHang"
                                    ,"@TongTienThue","@TongChietKhau","@GhiChu","@NguoiChinhSuaId","@NgayChinhSua"
                                };
            Object[] valueofParameter = new Object[] {
                                     PhieuThu.PhieuThuId,PhieuThu.SoPhieuThu,PhieuThu.KyHieu,PhieuThu.Ky,PhieuThu.NgayXuat,PhieuThu.KhachHangId,PhieuThu.MaKhachHang,
                                    PhieuThu.HoTen,PhieuThu.MaSoThue, PhieuThu.DiaChi,PhieuThu.PhanTramThue, PhieuThu.TongTienThu,PhieuThu.TongTienHang,
                                    PhieuThu.TongTienThue,PhieuThu.TongChietKhau, PhieuThu.GhiChu,PhieuThu.NguoiChinhSuaId, PhieuThu.NgayChinhSua
                                };
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            if (i != -1)
            {
                for (int k = 0; k < PhieuThu.CacPTCT.Count; k++)
                {
                    if (PhieuThuChiTietDAO.Sua(PhieuThu.CacPTCT[k]) == -1)
                    {
                        return -1;
                    }
                }
            }
            return i;
        }

        /// <summary>
        /// Xoa mot phieu thu 
        /// </summary>
        /// <param name="PTCT"></param>
        /// <returns></returns>
        public int Xoa(decimal PhieuThuId)
        {
            int i = -1;
            string sql = "spDeletePhieuThu";
            string[] ParameterColection = new string[] { "@PhieuThuId" };
            Object[] valueofParameter = new Object[] { PhieuThuId };
            if (PhieuThuChiTietDAO.Xoa(PhieuThuId) == -1)
            {
                return -1;
            }
            else
            {
                i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
                if (i != -1)
                {
                    i = new clsCongNoCauLongDAO().CapNhatCongNoKhiXoaPhieuThu(PhieuThuId) ;
                }

            }
            return i;
        }

        /// <summary>
        /// Xoa tam thoi mot phieu thu 
        /// </summary>
        /// <param name="PTCT"></param>
        /// <returns></returns>
        public int XoaTamThoi(decimal PhieuThuId)
        {
            int i = -1;
            string sql = "spDeletePhieuThuTheoMaPhieuThu";
            string[] ParameterColection = new string[] { "@PhieuThuId" };
            Object[] valueofParameter = new Object[] { PhieuThuId };
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            if (i != -1)
            {
                i = new clsCongNoCauLongDAO().CapNhatCongNoKhiXoaPhieuThu(PhieuThuId);
            }
            return i;
        }

        /// <summary>
        /// Xoa tam thoi mot phieu thu 
        /// </summary>
        /// <param name="PTCT"></param>
        /// <returns></returns>
        public int XoaTamThoi(string SoPhieuThu, string KyHieu)
        {
            int i = -1;
            clsPhieuThuTienDTO PhieuThu = LayThongMotPhieuThu(SoPhieuThu, KyHieu);
            if (PhieuThu != null && PhieuThu.PhieuThuId>0)
            {
                string sql = "spDeletePhieuThuTheoSoPhieuThu";
                string[] ParameterColection = new string[] { "@SoPhieuThu", "@KyHieu" };
                Object[] valueofParameter = new Object[] { SoPhieuThu, KyHieu };
                i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
                if (i != -1)
                {
                    i = new clsCongNoCauLongDAO().CapNhatCongNoKhiXoaPhieuThu(PhieuThu.PhieuThuId);
                }
            }
            return i;
        }

        public bool KiemTraTonTai(string SoPhieuThu, string KyHieu)
        {
            // Tạo danh sách "Tên các Parameter" đầu vào để thực hiện store
            string[] Parameters = new string[] { "@SoPhieuThu", "@KyHieu" };
            // Gán giá trị cho các Parameter" vừa được tạo ở trên
            object[] ValueOfParameters = new object[] { SoPhieuThu, KyHieu };
            // Gọi thực hiện lấy danh sách phiếu thu thu tiền 
            DataTable result = sqlServer.readData("spGetInfoPhieuThuTheoSoPhieuThuVaKyHieu", Parameters, ValueOfParameters);
            //Kiểm tra số dòng đọc được trong database
            if (result.Rows.Count > 0)
            {
                //phiếu thu này tồn tại trong CSDL
                return true;
            }
            else
            {
                //phiếu thu này không tồn tài trong CSDL
                return false;
            }
        }

        public DataTable ThongKeDoanhThuTheoLoaiDinhKhoanCo(DateTime? TuNgay, DateTime? DenNgay)
        {
            string sql = "spGetBangThongKeDoanhThuTheoDinhKhoanCo";
            string[] ParameterColection = new string[] {    "@TuNgay","@TuDen"};
            Object[] valueofParameter = new Object[] { TuNgay,DenNgay};
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            return table;
        }

        public DataTable ThongKeDoanhThuVaChiPhi(DateTime? TuNgay, DateTime? DenNgay)
        {
            string sql = "spGetBangThongKeDoanhThuVaChiPhi";
            string[] ParameterColection = new string[] { "@TuNgay", "@TuDen" };
            Object[] valueofParameter = new Object[] { TuNgay, DenNgay };
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            return table;
        }

        public DataTable ThongKeDoanhThuVaChiPhi()
        {
            string sql = "spGetBangThongKeTatCaDoanhThuVaChiPhi";
            DataTable table = sqlServer.readData(sql);
            return table;
        }

        public DataTable ThongKeDoanhThuVaChiPhiTheoNam(DateTime? TuNgay, DateTime? DenNgay)
        {
            string sql = "spGetBangThongKeTatCaDoanhThuVaChiPhiTheoNam";
            string[] ParameterColection = new string[] { "@TuNgay", "@TuDen" };
            Object[] valueofParameter = new Object[] { TuNgay, DenNgay };
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            return table;
        }
    }
}
